SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[usp_StatisticsCompanySearch]
    (
      @param_JOL_Statistics_StartTime VARCHAR(20),
      @param_JOL_Statistics_EndTime VARCHAR(20)
    )
AS
	SET @param_JOL_Statistics_StartTime = @param_JOL_Statistics_StartTime + '-01'
	SET @param_JOL_Statistics_EndTime = @param_JOL_Statistics_EndTime + '-01'
    SELECT 
		Statistics_EmployerID AS JOL_Statistics_EmployerID,
		JOL_Employer_Account AS JOL_Statistics_EmployerName,
		SUM(Statistics_RequireNUM) + SUM(Statistics_RequirePeopleNUM) + SUM(Statistics_ApplyFieldNUM) + SUM(Statistics_LectureNUM) + SUM(Statistics_Recruitment) +  SUM(Statistics_AgreementNUM) AS JOL_Statistics_SubTotal,
		SUM(Statistics_RequireNUM) AS JOL_Statistics_RequireNUM,
		SUM(Statistics_RequirePeopleNUM) AS JOL_Statistics_RequirePeopleNUM,
		SUM(Statistics_ApplyFieldNUM) AS JOL_Statistics_ApplyFieldNUM,
		SUM(Statistics_LectureNUM) AS JOL_Statistics_LectureNUM,
		SUM(Statistics_Recruitment) AS JOL_Statistics_Recruitment,
		SUM(Statistics_AgreementNUM) AS JOL_Statistics_AgreementNUM
    FROM  dbo.JOL_Statistics_EmployerActivity INNER JOIN dbo.JOL_Employer ON Statistics_EmployerID = JOL_Employer_No
    WHERE @param_JOL_Statistics_StartTime <= Statistics_Date AND Statistics_Date <= @param_JOL_Statistics_EndTime
    GROUP BY Statistics_EmployerID, JOL_Employer_Account
    ORDER BY JOL_Statistics_SubTotal DESC, JOL_Statistics_AgreementNUM DESC, JOL_Statistics_RequireNUM DESC, 
			JOL_Statistics_RequirePeopleNUM DESC, JOL_Statistics_ApplyFieldNUM DESC, JOL_Statistics_LectureNUM DESC, 
			JOL_Statistics_Recruitment DESC, JOL_Employer_Account
	RETURN

GO
